Mega Code Archive
Language Basics
1) A counting variable I is initialized just prior to the loop and incremented inside the loop
2) A function returns a value to whomever called it
3) A function returns a value to whomever called it We can recast the previous sub into a function as follows
4) A function with one argument
5) A Generic Procedure Skeleton with Basic Error Handling
6) A GoTo statement with a condition
7) A line number is simply a number placed at the beginning of a line to identify it For example, consider this demonstration
8) A nested Select Case example
9) A recursive procedure is one that calls itself
10) A simple decision-making structure in a subroutine
11) A Simple Error-Handling Routine
12) A single statement splits over four lines
13) Accommodating for Missing Parameters in Your Code
14) Add the Information icon
15) Adding a Help Button to a Message Box
16) After you have placed the return value into a variable, you can easily introduce logic into your program to respond to the
17) An Example of Code Without Error Handling
18) An Example of Error Handling Using the On Error GoTo Statement
19) An Exit Sub just before the error label, which forces the subroutine to exit immediately, without erroneously running the
20) An If Then ElseIf Statement without an Else Clause
21) Any mathematical operation that can be performed on a number can be performed on a numerical variable
22) Assert
23) Assigning Values to Variables
24) Block If Statements
25) Buttons and Icons in the MsgBox Function
26) ByVal and ByRef
27) Call MsgBox function by using its parameter name
28) Call user-defined function in MsgBox
29) Calling Functions and Sub Procedures
30) Calling the function from a Sub procedure
31) Check MsgBox result
32) Check the error number
33) Check the error number with Select Case statement
34) Checks for the Cancel button clicks and takes no action
35) Checks values in a range 10 rows by 5 columns
36) Choosing Actions with Select Case
37) Choosing an Action Using IIf(expr, truepart, falsepart)
38) Combine InputBox and Do Loop to read user input
39) Combine several If structures using ElseIf
40) Common Mathematical Operators Used in VBA
41) Comparison operators in VBA
42) Constants can be declared on one line if separated by commas
43) Convert Fahrenheit To Celsius
44) Convert number to String by using Select statement
45) Create an error, and then query the object for the error number and description
46) Creating a User-Defined Error
47) Creating Procedures
48) Creating Your Own VBA Functions
49) Deal with the error
50) Decisions Based on More Than One Condition
51) Declare several variables on the same line, separating each variable name with a comma
52) Declare sub
53) Declaring Variables
54) Default value parameters
55) Define a string variable
56) Define an integer
57) Determines which button was pressed
58) Do Loop Until
59) Do Loop While
60) Do while loop
61) Double type parameter
62) Each of the available buttons will produce an integer result
63) Early Exit from a Loop
64) EBEngine Errors
65) Error handling by checking the Error code
66) Execute the code block until the conditional statement is True
67) Executing a Function Procedure from a Subroutine
68) Exit Do Until Loop
69) Exiting a For Next Loop Early
70) For Each Variation
71) For loop whose step is 2
72) For Loops with different steps
73) For Next Loop
74) Forcing Declaration of Variables
75) Function takes two argumentss length and widths and calculates the total area by multiplying them together
76) Functions That Return an Array
77) Get the Error source
78) Get the Password
79) Get the return value from InputBox
80) Getting a response from a message box with Select Case
81) How a GoTo statement works
82) handle error caused by value from InputBox
83) If statement ladder
84) If Then ElseIf Else Statements
85) If Then ElseIf Statement
86) If you need a procedural level variable to retain its value between calls to the procedure, declare the variable using the
87) If youre going to declare multiple variables on one line, make sure each variable is specifically declared
88) IfThenElse
89) IfThenElse with Comparison operators
90) Ignoring an Error and Continuing Execution
91) Illustrate a reason why you might want to pass a parameter by reference
92) Inserting Default Values When Parameters Are Missing
93) It is a good idea to include the title and default arguments to provide the user with a little help in knowing what to ent
94) Iterates four times outputting the numbers 0, 3, 6, and 9 in a message box
95) Iterates six times outputting the numbers 0, 2, 4, 6, 8, and 10 in a message box
96) Keep the message box visible while the user works with other open applications
97) Local variable
98) Local variables are available only in the procedure where they are declared
99) Logical Expressions
100) Logical operators in VBA
101) Looking Up the Call Stack for a Previous Error Handler
102) Loop through ranges
103) Looping through the elements in an array
104) Make a constant available to all modules in your application, use the Public keyword in front of the Const statement
105) Mix integer and string in MsgBox
106) Module level variable
107) Module-level variable declared with Private statement
108) Move through the Errors collection and display properties of each Error object
109) MsgBox Welcome to VBA, vbYesNoCancel, VBA Message Box
110) MsgBox with parameter Name
111) MsgBox with three parameters
112) MsgBoxs third parameter is the message boxs title Its fourth and fifth parameters are the Help file and context ID
113) Named parameters
114) Nest an if statement with Do While
115) Nest if statement in a Do Loop
116) Nest if statement into a for statement
117) Non integer step for loop
118) Object parameter
119) Ok To Overwrite
120) One of simplest possible custom functions
121) One-Line If Statements
122) Only place parentheses around the arguments when calling a function and making use of the return value from the function p
123) Operator
124) Optional parameters
125) Parameters
126) Pass array parameter to a function
127) Pass by value
128) Pass double to sub module
129) Pass single parameter to MsgBox
130) Pass string value through Parameters
131) Pass variable by value
132) Passing Arguments to Subroutines and Functions
133) Passing Arguments with ByVal and ByRef
134) Passing Arrays as Parameters
135) Passing by Reference Versus Passing by Value
136) Passing Elements of an Array to Another Procedure
137) Passing Parameters and Returning Values
138) Placing a Resume Next Statement in Your Error Handler
139) Private (module-level) variables can be seen by any routine in the module they were declared in, but not from other module
140) Private module variable
141) Procedure-Level (Local) Variables
142) Properties of the Err Object
143) Provide help file for InputBox
144) Public Variables
145) Put InputBox in a Do Loop
146) Raising an Error
147) Read data from dialog box and fill it into the cell
148) Read integer number from InputBox
149) Read password from InputBox
150) Read user choice when dealing with error
151) Recursive function to calculate the faculty of x
152) Recursive Procedures
153) Redefine static variable
154) Reference InputBox from Application
155) Return a number from InputBox
156) Return a string from funtion
157) Return number from function
158) Return value from user-defined function
159) Returning a sorted list
160) Returning Values from the MsgBox Function
161) RunTime Error 1004
162) Runtime Error 9
163) Save the return value from MsgBox to a variable
164) Select Case Else
165) Select Case in a function
166) Select Case Statements with comparison operator
167) Select Case with Comparison operators
168) Set default button for MsgBox
169) Show Error discription in MsgBox
170) Show Error Number
171) Specify a range in select case statement
172) Specifying a Help File for a Message Box
173) Specifying a Range of Values in a Case Clause by using the To keyword between the values of expressions
174) Specifying a Title for a Message Box
175) Specifying Multiple Expressions in a Case Clause
176) Specifying the Data Type for a Functions Result
177) Static variable lifetime
178) Static Variables
179) Sub module scope
180) Substitute a default value if an optional parameter is not provided
181) The Call Statement
182) The For Next construct is used when you have an exact number of iterations you want to perform
183) The Immediate If (IIf)
184) The InputBox function displays a dialog box containing a simple text box
185) The loop iterates just three times outputting the numbers 10, 5, and 0 in a message box
186) The Mail your Error Routine
187) The message box displays Yes, No, and Cancel buttons
188) The MsgBox buttons arguments settings
189) The WithEnd With Structure
190) Three-level nested Select Case statement
191) To declare a variable in VBA use the Dim (short for Dimension) statement
192) To ensure that the code block executes at least once, place the While keyword and conditional statement at the end of the
193) To make a constant available within a single procedure, you declare it at the procedure level
194) To make sure that the user has chosen the OK button, check that the input box hasnt returned a zero-length string
195) To retrieve input from an input box, declare the numeric variable or String variable that will contain it
196) Tools for Debugging Code
197) Trap the error with On Error GoTo
198) Truth table for the AND operator
199) Truth table for the NOT operator
200) Truth table for the OR operator
201) Try again in case an error
202) Types of Variables
203) Understanding and Using Static Variables
204) Understanding Module-Level Variables
205) Use #Const to define the constant
206) Use #If
207) Use & to link messages
208) Use a constant in all the procedures of a module, use the Private keyword in front of the Const statement
209) Use a Constant to store the PI
210) Use a With End With construct with fully qualified properties
211) Use and in if statement
212) Use Boolean value in an If statement
213) Use Call to invoke another sub module
214) Use Chr() function to format MsgBox
215) Use Debug Print to output the message to the immediate window
216) Use Do Until with if statement
217) Use ElseIf in a function
218) Use GoTo to do a loop
219) Use If and ElseIf to check the parameter
220) Use If and IsNumeric function to check user input
221) Use IIF to calculate value
222) Use IIF(conditional test, value for True, value for False)
223) Use InputBox to read value for an array
224) Use Integer as the switch value for Select Case Structure
225) Use Integer variable as the loop control variable
226) Use if statement in a do loop
227) Use nested with statement
228) Use parameter name in InputBox function
229) Use select case statement
230) Use string text as the select case statement control value
231) Use string text value as the compiler directive
232) Use the MsgBox function result without using a variable
233) Uses a Resume statement after an error occurs
234) Using an Exit Do Statement
235) Using Assertions
236) Using Conditional Compilation
237) Using If Then Else Conditional Statement
238) Using Is with the Case Clause
239) Using Nested Loops
240) Using Optional Arguments
241) Using Parameter Arrays
242) Using Procedures and Subprocedures
243) Using Resume Conditionally Based on User Feedback
244) Using the Do Until Loop
245) Using the Do Until Loop with a Condition at the Bottom of the Loop
246) Using the Do While Loop
247) Using the Do While Loop with a Condition at the Bottom of the Loop
248) Using the For Each Next Loop
249) Using the If Then Statement
250) Using the InputBox Function
251) Using the LogError Routine
252) Using the MsgBox Function
253) Using the MsgBox Function with Arguments
254) Using the Multi-Line If Then Statement
255) Using the On Error Resume Next statement
256) Using the Resume LineLabel Statement to Specify Where Execution Continues After an Error Occurs
257) Using the Select Case Statement to check the function parameter
258) Using the Select Case Statement to check the MsgBox button clicked
259) Using Variables
260) Validate the users entry
261) Validation with the InputBox() Function
262) Variable declaration is required with Option Explicit and a module level variable (answer) is declared
263) Variable number of parameters
264) Variables can also be declared in the declarations section at the top of a module
265) Viewing the Errors Stored in the Errors Collection
266) When declaring a constant, you can use any one of the following data types
267) While Wend Loops
268) With the compiler directive
269) Within an If structure, you can have an alternative path by using an Else statement
270) Working with Date type value Constants
271) Working with pre-defined Constant
272) Working with Symbolic Constants
273) Working with the Screen Object
274) Write If Then statement in one line
275) Writing a Function Procedure with Arguments
276) Writing Information to a Textual Error Log File
277) Writing VBA Function Procedures
278) You can go backward and use increments other than 1